<?php defined('SYSPATH') or die('No direct script access.');
/**
 * 
 * 采购付款单批量上传
 * @author locove
 *
 */
class Controller_Report_Onway extends Controller_Admin{
    /**
     * (non-PHPdoc)
     * @see Controller_Admin::before()
     */
	public function before()
    {
    	try {
    		  ACL::Required('onway');
    	} catch ( HTTP_Exception_403 $e ) {
				Message::error('sorry，你没有权限进这个目录');
    		$this->request->redirect ( URL::base (true) );
    	}
        parent::before();
    }
    
    /**
     * 首页
     */
    public function action_index()
    {
		$this->title='库存销量查询';
		$view=View::factory('report/onway');
		if(isset($_GET['errors'])){
			$view->set('errors',$_GET['errors']);
		}
		$this->response->body($view);	
	}
	/**
	 * 下载模板
	 */
	public function action_down_template()
	{
		$this->title='下载模版';
		require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
		$this->objReader  = PHPExcel_IOFactory::createReader('Excel5');
		$objPHPExcel      = new PHPExcel();
		$objPHPExcel->getProperties()->setCreator("newerp")
		->setLastModifiedBy("newerp")
		->setTitle("Office 2003 XLSX Test Document")
		->setSubject("Office 2003 XLSX Test Document")
		->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
		->setKeywords("office 2003 openxml php")
		->setCategory("Test result file");
		$objActSheet =$objPHPExcel->setActiveSheetIndex(0);
		$objActSheet->setCellValue('A1', 'SKU');
		$objPHPExcel->getActiveSheet()->setTitle('load');
		$objPHPExcel->setActiveSheetIndex(0);
		$day=date("m-d");
        ob_end_clean();
        header("Content-Type: application/vnd.ms-excel; charset=utf-8");
		header('Content-Disposition: attachment;filename="'.$day.'在途库存.xls"');
		header('Cache-Control: max-age=0');
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save('php://output');
		exit;			
	}
	/**
	 * 导入sku查询
	 */
	public function action_load()
	{
		set_time_limit(0);
	 	ini_set('memory_limit','1152M');
        ini_set('max_allowed_packet','33M');
	 	require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
	 	$xls= $_FILES['xls']['tmp_name'];
	 	$objPHPExcel= PHPExcel_IOFactory::load($xls);
	 	$data= $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
		$num = count($data);
		$error_arr=array();//存储不合格的行
		$begin_time=time();
		$skus_execl=$skus_arr=$error_num=$sku_error_arr=array();
		
		$sku_str='';
		for($i=2;$i<=$num;$i++){
//			$sku.=trim($data[$i]['A']);
//			if(Deliverycheck::check_sku($sku)){
//			}else{
//				$error_arr[]=$i;
//			}
			$skus_execl[$i-2]= strtoupper(trim($data[$i]['A']));
			$sku_str.=$sku_str==''?"'".strtoupper(trim($data[$i]['A']))."'":",'".strtoupper(trim($data[$i]['A']))."'";
		}
		if($sku_str!=''){
            //START     ----------------------------------CWY更新代码-------------------------------------
            $skus_arr = Skusystemapi::getSkuIn($sku_str);
//			$sql="select upper(trim(products_sku)) as sku
//				from erp_products_data
//				where products_sku<>''
//				and products_sku in (".$sku_str.")";
//			$skus_arr=DB::query(Database::SELECT,$sql)->execute('readonlyerp')->as_array();
            //END     ----------------------------------CWY更新代码-------------------------------------
			if(!empty($skus_arr)){
                $values = array();

                foreach ($skus_arr as $row)
                {
                    if (isset($row['sku']) && !empty($row['sku']))
                    {
                        $values[] = strtoupper($row['sku']);
                    }
                }

                $skus_arr =  $values;
				$sku_error_arr = array_diff($skus_execl,$skus_arr);
				if($sku_error_arr)
				{
					$error_num = array_keys($sku_error_arr);
				}
			}
		}

//		if(empty($error_num))
//		{
			$daysales_all = $actual_num_all = $op_print_num_all = $onway_all = array();
			if($sku_str)
			{
				//实际库存信息
				$actual_num_all = Warningtmp::get_inventory($skus_arr);

				//订单占用库存信息
				$op_print_num_all = Warningtmp::get_print_num($skus_arr);
				
				
				//在途库存信息
				$onway_all = Warningtmp::get_onway_num($skus_arr);

				//销量信息
				$daysales_all_string = "SELECT sku,daysales,daysales7,daysales14,daysales28 FROM product_attached 
				WHERE sku in(".$sku_str.")";
				
				$return = DB::query(Database::SELECT,$daysales_all_string)->execute('readonlyerp')->as_array();
				
				if($return)
				{
					foreach ($return as $value)
					{
						$daysales_all[$value['sku']]['daysales'] = $value['daysales'];
						$daysales_all[$value['sku']]['daysales7'] = $value['daysales7'];
						$daysales_all[$value['sku']]['daysales14'] = $value['daysales14'];
						$daysales_all[$value['sku']]['daysales28'] = $value['daysales28'];
					}
				}
			}
	 		$objPHPExcel      = new PHPExcel();
	 		$objPHPExcel->getProperties()->setCreator("YKS")
	 		->setLastModifiedBy("YKS")
	 		->setTitle("Office 2003 XLSX Test Document")
	 		->setSubject("Office 2003 XLSX Test Document")
	 		->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
	 		->setKeywords("office 2003 openxml php")
	 		->setCategory("Test result file");
	 		$objActSheet =$objPHPExcel->setActiveSheetIndex(0);
	 		$objActSheet->setCellValue('A1', 'SKU');
	 		$objActSheet->setCellValue('B1', '在途库存');
	 		$objActSheet->setCellValue('C1', '日均销量');
	 		$objActSheet->setCellValue('D1', '7天销量');
	 		$objActSheet->setCellValue('E1', '14天销量');
	 		$objActSheet->setCellValue('F1', '28天销量');
	 		$objActSheet->setCellValue('G1', '实际库存数量');
	 		$objActSheet->setCellValue('H1', '总未发数量');
	 		$objActSheet->setCellValue('I1', '库存可用数量');
	 		$objActSheet->setCellValue('J1', '总可用数量');

			foreach($skus_arr as $num => $sku){
	 			$objActSheet->setCellValue('A'.(($num+2)), $sku);
	 			$onway_temp=(isset($onway_all[$sku])&&$onway_all[$sku]>0)?$onway_all[$sku]:0;
		 		$objActSheet->setCellValue('B'.($num+2), $onway_temp);
		 		$objActSheet->setCellValue('C'.($num+2), isset($daysales_all[$sku]['daysales'])?$daysales_all[$sku]['daysales']:0);
				$objActSheet->setCellValue('D'.($num+2), isset($daysales_all[$sku]['daysales7'])?$daysales_all[$sku]['daysales7']:0);
		 		$objActSheet->setCellValue('E'.($num+2), isset($daysales_all[$sku]['daysales14'])?$daysales_all[$sku]['daysales14']:0);
		 		$objActSheet->setCellValue('F'.($num+2), isset($daysales_all[$sku]['daysales28'])?$daysales_all[$sku]['daysales28']:0);
		 		
		 		$actual_temp=isset($actual_num_all[$sku])?$actual_num_all[$sku]:0;
		 		$op_print_num_temp=isset($op_print_num_all[$sku])?$op_print_num_all[$sku]:0;
		 		$keyong=$actual_temp-$op_print_num_temp;
		 		$objActSheet->setCellValue('G'.($num+2), $actual_temp);
		 		$objActSheet->setCellValue('H'.($num+2), $op_print_num_temp);
				$objActSheet->setCellValue('I'.($num+2), $keyong);
		 		$objActSheet->setCellValue('J'.($num+2), $keyong+$onway_temp);
			
	 		}
	 		if($sku_error_arr){
	 			$error_num=0;
	 			$numbers=count($skus_arr)+2;
				$objActSheet->setCellValue('A'.$numbers, '以下SKU找不到或者找不到数据');
	 			$error_num++;
				foreach($sku_error_arr as $_sku){
		 			$objActSheet->setCellValue('A'.($numbers+$error_num), $_sku);
					$error_num++;	 				
		 		}
	 		}
	 		$objPHPExcel->getActiveSheet()->setTitle('在途库存报表');
			$objPHPExcel->setActiveSheetIndex(0);
	 		$day=date("m-d");
	 		$filename=$day.'在途库存报表'.'.xls';
        ob_end_clean();
        header("Content-Type: application/vnd.ms-excel; charset=utf-8");
			header('Content-Disposition: attachment;filename='.$filename);
			header('Cache-Control: max-age=0');
			$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
			$objWriter->save('php://output');
			exit;			
//		}else{
//			//记录有误的sku
//			$errors='';
//			foreach ($error_num as $_error){
//				$errors.=$errors==''?$_error:','.$_error;
//			}
//			$this->request->redirect('report/onway/index?errors='.$errors);
//		}
	}
}
